DROP FUNCTION main.validate_sample_id();
CREATE OR REPLACE FUNCTION main.validate_sample_id()
/* ---------------------------------------------------------------------------
       Validate sample_id in samples
*/ ---------------------------------------------------------------------------
  RETURNS trigger AS
$BODY$
    DECLARE 
    invalid_sample_id text; -- This variable will store output of queries cheking for invalid sample_id. It must remain NULL for valid IDs;
    
    BEGIN
    
    -- Get possible sample_ids whose initials are not in contacts or are not capitalized.     
    SELECT samples.sample_id INTO invalid_sample_id
    FROM samples LEFT JOIN contacts ON
    upper(contacts.initials) = upper(regexp_replace(samples.sample_id, '\d{1,}.*', '')) -- regexp to extract the initials.
    WHERE upper(contacts.initials) IS NULL
    LIMIT 1;

    IF invalid_sample_id IS NOT NULL THEN
        RAISE EXCEPTION 'Trigger validate_sample_id: sample_id "%" seems to start with initials not found in table contacts.initials or initials not capitalized', invalid_sample_id;
        RETURN NULL;
    END IF;

    -- Check if sample_ids are composed by two or more letters followed by 3 or more digits possibly followed by _ and only digit and letters.
    -- Changed on 19/11/2013: Initials are no longer case sensitive.
    SELECT samples.sample_id INTO invalid_sample_id
    FROM samples WHERE sample_id !~ '(^[a-zA-Z]{2,}[0-9]{3,}$)|([a-zA-Z]{2,}[0-9]{3,}_\w+$)';

    IF invalid_sample_id IS NOT NULL THEN
        RAISE EXCEPTION 'Trigger validate_sample_id: sample_id "%" seems to be badly formatted. Allowed format must match the regexp "(^[a-zA-Z]{2,}[0-9]{3,}$)|([a-zA-Z]{2,}[0-9]{3,}_\w+$)"', invalid_sample_id;
        RETURN NULL;
    END IF;
    RETURN NULL;    
    END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;

/* ---------------------------------------------------------------------------- */

drop trigger if exists trigger_validate_sample_id on samples;
create constraint trigger trigger_validate_sample_id  after insert or update
    on samples deferrable initially deferred for each row
    execute procedure validate_sample_id();


/* ----------------------------------------------------------------------------
*    UNIT TEST:
*    validate_sample_id();
*    set return value to text before running these tests.
* -----------------------------------------------------------------------------
*/
begin; -- OK: even if db is not capitalized
update samples set sample_id = 'db001_test2' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Must fail because dbx not in initials
update samples set sample_id = 'DBX001_test2' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Must fail because '.' not allowed in sample_id
update samples set sample_id = 'DB001._test2' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Fail: sample_id can't end with _
update samples set sample_id = 'DB001_' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Ok
update samples set sample_id = 'DB001' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Ok
update samples set sample_id = 'DB001_ok' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;

begin; -- Ok
update samples set sample_id = 'DB001_123' where sample_id = 'DB001_test_do_NOT_delete_me';
select validate_sample_id();
rollback;
------------------------ END OF UNIT TEST -------------------------------------